package com.baoming.tool;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map; 

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.struts2.ServletActionContext;

import com.baoming.domain.Properties;
import com.baoming.domain.SingupEnter;

public class ExcellentTool {
	
	private static HSSFWorkbook createWorkBook(List<SingupEnter> enters,List<Properties> props , String name){
		
		HSSFWorkbook wb = new HSSFWorkbook() ; 
		HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);    
		HSSFSheet sheet = wb.createSheet(name) ;  
		
		HSSFRow headerRow = sheet.createRow(0) ; 
		
		HSSFFont font =wb.createFont();
        font.setFontHeightInPoints((short) 11); //字体高度
        font.setColor(HSSFFont.COLOR_NORMAL); //字体颜色
        font.setFontName("宋体"); //字体
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
		HSSFCellStyle style2 = wb.createCellStyle();
		style2.setFont(font) ; 
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        //生成标题 
		for(int i = 0 ;i <= props.size() ; i ++){ 
			 
			//处理时间
			if(i == props.size()){
				HSSFCell noCell = headerRow.createCell((short) i) ; 
				String headerStr = "报名时间" ; 
				HSSFRichTextString headerCellStr = new HSSFRichTextString(headerStr) ;
				noCell.setCellStyle(style2) ;
				noCell.setCellValue(headerCellStr) ;  
				sheet.setColumnWidth((short)i, (short)(52*100)) ;  
				continue ; 
			}
			
			HSSFCell noCell = headerRow.createCell((short) i) ; 
			 
			String headerStr ; 
			if(props.get(i).getAlias() != null && !"".equals(props.get(i).getAlias())){
				headerStr = props.get(i).getAlias() ; 
			}else{
				headerStr = props.get(i).getValue() ; 
			}
			HSSFRichTextString headerCellStr = new HSSFRichTextString(headerStr) ;
			noCell.setCellStyle(style2) ;
			noCell.setCellValue(headerCellStr) ;  
			sheet.setColumnWidth((short)i, (short)(52*100)) ;  
			
		}
		 
		//导出内容 
		for(int j = 0 ;j < enters.size() ; j ++){ 
			
			HSSFRow row = sheet.createRow(j+1) ; 
			Map<String, String> map = enters.get(j).getMap() ; 
			int i = 0 ; 
			for(i = 0 ;i < props.size() ; i ++){ 
				
				Properties prop = props.get(i) ;  
				
				HSSFCell propCell = row.createCell((short) i) ; 
				propCell.setCellStyle(style) ; 
				HSSFRichTextString propCellStr = new HSSFRichTextString(map.get(prop.getName())) ; 
				propCell.setCellValue(propCellStr) ; 
				 
			} 
			
			//最后结局添加时间
			HSSFCell propCell = row.createCell((short) (i)) ; 
			propCell.setCellStyle(style) ; 
			HSSFRichTextString propCellStr = new HSSFRichTextString(enters.get(j).getAddTime()) ; 
			propCell.setCellValue(propCellStr) ; 
			
		}
		return wb ;
	}

	public static String createExcel(List<SingupEnter> enters, List<Properties> props , String singupId) {
		// TODO Auto-generated method stub
		
		List<SingupEnter> enterlist = XmlTool.createList(enters) ; 
		SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
		
		//得到所有数组   
		HSSFWorkbook workbook = createWorkBook(enterlist , props , "test.excel");
		
		String realPath = "excel/"+formatter.format(new java.util.Date()) ; 
		String abcPath = ServletActionContext.getServletContext().getRealPath(realPath) ; 
		
		//如果本地文件夹找不到 则创建 
		File file = new File(abcPath) ; 
		if(!file.exists()){
			file.mkdir() ;
		}
		
		String fileName = "/"+ singupId +".xls" ; 
		
		//如果已经存在 则删除 
		File oldFile = new File(abcPath+fileName);
		if(oldFile.exists()){
			oldFile.delete() ; 
		}
		
		//保存本地 
		try {
			FileOutputStream fileOut = new FileOutputStream(abcPath+fileName);  
			workbook.write(fileOut);     
			fileOut.close();
		} catch (IOException e) { 
			e.printStackTrace();
		}     
 
		return realPath+fileName ;
	}

}
